import pandas as pd
import numpy as np
from datetime import datetime


# 工单编号：大数据 - 八维保险数据挖掘 - 07 - 人寿保险盈利能力

class InsuranceProfitabilityAnalyzer:
    def __init__(self):
        # 模拟数据生成
        self.data = self.generate_mock_data()
        self.calculated_metrics = {}

    def generate_mock_data(self):
        """生成模拟保险业务数据"""
        dates = pd.date_range(start='2024-01-01', end='2025-02-28', freq='ME')
        periods = len(dates)

        data = pd.DataFrame({
            # 基础财务数据
            'report_date': dates,
            'net_profit': np.random.uniform(500, 2000, periods).round(2),  # 净利润
            'total_assets': np.random.uniform(10000, 50000, periods).round(2),  # 总资产
            'owner_equity': np.random.uniform(8000, 30000, periods).round(2),  # 所有者权益
            'revenue': np.random.uniform(5000, 15000, periods).round(2),  # 营业收入(已赚保费)

            # 保费相关数据
            'earned_premium': np.random.uniform(4000, 12000, periods).round(2),  # 已赚保费
            'original_premium': np.random.uniform(4500, 13000, periods).round(2),  # 原保费收入
            'receivable_premium': np.random.uniform(300, 1500, periods).round(2),  # 应收保费余额

            # 承保利润相关
            'claim_expense': np.random.uniform(2000, 6000, periods).round(2),  # 赔付支出
            'reinsurance_income': np.random.uniform(100, 500, periods).round(2),  # 分保费用收入
            'reinsurance_expense': np.random.uniform(50, 300, periods).round(2),  # 分保费用支出
            'commission_expense': np.random.uniform(300, 1000, periods).round(2),  # 手续费及佣金支出
            'management_expense': np.random.uniform(400, 1200, periods).round(2),  # 业务及管理费
            'tax_expense': np.random.uniform(200, 600, periods).round(2),  # 税金及附加

            # 再保数据
            'ceded_premium': np.random.uniform(500, 2000, periods).round(2),  # 分出保费
            'total_premium': np.random.uniform(5000, 15000, periods).round(2),  # 总保费

            # 内含价值相关
            'effective_business_value': np.random.uniform(10000, 40000, periods).round(2)  # 有效业务价值
        })

        return data.sort_values('report_date')

    def calculate_metrics(self):
        """计算所有业务指标"""
        df = self.data.copy()

        # 计算净资产收益率
        df['avg_owner_equity'] = (df['owner_equity'] + df['owner_equity'].shift(1)) / 2
        df['roe'] = (df['net_profit'] / df['avg_owner_equity']) * 100

        # 计算总资产收益率
        df['avg_total_assets'] = (df['total_assets'] + df['total_assets'].shift(1)) / 2
        df['roa'] = (df['net_profit'] / df['avg_total_assets']) * 100

        # 计算总资产周转率
        df['asset_turnover'] = (df['revenue'] / df['avg_total_assets']) * 100

        # 计算应收保费率 (使用滚动12个月数据)
        df['rolling_12m_premium'] = df['original_premium'].rolling(window=12, min_periods=1).sum()
        df['receivable_premium_ratio'] = (df['receivable_premium'] / df['rolling_12m_premium']) * 100

        # 计算应收保费周转率
        df['avg_receivable_premium'] = (df['receivable_premium'] + df['receivable_premium'].shift(1)) / 2
        df['receivable_turnover'] = (df['original_premium'] / df['avg_receivable_premium']) * 100

        # 计算内含价值
        df['embedded_value'] = df['owner_equity'] + df['effective_business_value']

        # 计算总资产增长率
        df['total_asset_growth'] = ((df['total_assets'] - df['total_assets'].shift(1)) / df['total_assets'].shift(
            1)) * 100

        # 计算净资产增长率
        df['equity_growth'] = ((df['owner_equity'] - df['owner_equity'].shift(1)) / df['owner_equity'].shift(1)) * 100

        # 计算承保利润率
        df['underwriting_profit'] = (df['earned_premium'] - df['claim_expense'] +
                                     df['reinsurance_income'] - df['reinsurance_expense'] -
                                     df['commission_expense'] - df['management_expense'] -
                                     df['tax_expense'])
        df['underwriting_margin'] = (df['underwriting_profit'] / df['earned_premium']) * 100

        # 计算自留保费占净资产比
        df['net_retained_premium'] = df['total_premium'] - df['ceded_premium']
        df['retained_premium_ratio'] = (df['net_retained_premium'] / df['avg_owner_equity']) * 100

        # 存储计算结果
        self.calculated_metrics = df.dropna(subset=['avg_owner_equity']).copy()
        return self.calculated_metrics

    def export_to_excel(self, filename="人寿保险盈利能力分析.xlsx"):
        """导出结果到Excel文件"""
        if self.calculated_metrics.empty:
            self.calculate_metrics()

        # 创建Excel写入器
        with pd.ExcelWriter(filename, engine='openpyxl') as writer:
            # 主表：所有指标
            self.calculated_metrics.to_excel(writer, sheet_name='盈利能力指标', index=False)

            # 创建指标解释表
            metrics_explanation = pd.DataFrame({
                '指标名称': [
                    '净资产收益率(ROE)',
                    '总资产收益率(ROA)',
                    '总资产周转率',
                    '应收保费率',
                    '应收保费周转率',
                    '内含价值',
                    '总资产增长率',
                    '净资产增长率',
                    '承保利润率',
                    '自留保费占净资产比'
                ],
                '计算公式': [
                    '报告期净利润 ÷ [(期初所有者权益 + 期末所有者权益)÷2] × 100%',
                    '报告期净利润 ÷ [(期初总资产 + 期末总资产)÷2] × 100%',
                    '报告期营业收入 ÷ [(期初总资产 + 期末总资产)÷2] × 100%',
                    '账龄不长的应收保费余额 ÷ 滚动12个月原保费收入 × 100%',
                    '报告期原保费收入 ÷ [(期初应收保费余额 + 期末应收保费余额)÷2] × 100%',
                    '所有者权益 + 有效业务价值',
                    '(期末总资产 - 期初总资产) ÷ 期初总资产 × 100%',
                    '(期末所有者权益 - 期初所有者权益) ÷ 期初所有者权益 × 100%',
                    '承保利润 ÷ 已赚保费 × 100%',
                    '报告期自留保费 ÷ [(期初所有者权益 + 期末所有者权益)÷2] × 100%'
                ],
                '适用范围': [
                    '财险业务、人身险业务、再保业务',
                    '财险业务、再保人身险业务',
                    '财险业务、人身险业务、再保业务',
                    '财险业务、人身险业务',
                    '财险业务、人身险业务',
                    '人身险业务、再保人身险业务',
                    '财险业务、再保财险业务',
                    '财险业务、再保财险业务',
                    '所有业务类型',
                    '通用指标'
                ],
                '业务意义': [
                    '衡量公司运用自有资本的效率',
                    '评估公司利用全部资产的盈利能力',
                    '反映公司资产运营效率的指标',
                    '评估应收保费管理效率的指标',
                    '衡量应收保费回收速度的指标',
                    '寿险公司核心价值评估指标',
                    '反映公司规模扩张速度的指标',
                    '评估公司自有资本增长能力的指标',
                    '衡量承保业务盈利能力的关键指标',
                    '评估保费自留与资本匹配度的指标'
                ]
            })
            metrics_explanation.to_excel(writer, sheet_name='指标解释', index=False)

            # 创建数据字典
            data_dictionary = pd.DataFrame({
                '字段名': [
                    'net_profit', 'total_assets', 'owner_equity', 'revenue',
                    'earned_premium', 'original_premium', 'receivable_premium',
                    'ceded_premium', 'total_premium', 'effective_business_value',
                    'claim_expense', 'reinsurance_income', 'reinsurance_expense',
                    'commission_expense', 'management_expense', 'tax_expense'
                ],
                '中文名称': [
                    '净利润', '总资产', '所有者权益', '营业收入(已赚保费)',
                    '已赚保费', '原保费收入', '应收保费余额',
                    '分出保费', '总保费', '有效业务价值',
                    '赔付支出', '分保费用收入', '分保费用支出',
                    '手续费及佣金支出', '业务及管理费', '税金及附加'
                ],
                '数据来源': [
                    '财务系统', '资产负债表', '资产负债表', '利润表',
                    '保费系统', '保单系统', '应收系统',
                    '再保系统', '保单系统', '精算系统',
                    '理赔系统', '再保系统', '再保系统',
                    '财务系统', '财务系统', '税务系统'
                ]
            })
            data_dictionary.to_excel(writer, sheet_name='数据字典', index=False)

        print(f"分析结果已导出到: {filename}")
        return filename

    def generate_test_document(self):
        """生成测试文档内容（示例）"""
        test_doc = {
            "测试用例": [
                {
                    "测试指标": "净资产收益率",
                    "测试方法": "验证ROE计算逻辑是否正确",
                    "测试SQL": "SELECT report_date, net_profit, owner_equity, (net_profit / ((LAG(owner_equity) OVER (ORDER BY report_date) + owner_equity)/2) * 100 AS roe_calculated FROM insurance_data"
                },

                {
                    "测试指标": "内含价值",
                    "测试方法": "验证内含价值计算逻辑",
                    "测试SQL": "SELECT report_date, owner_equity, effective_business_value, owner_equity + effective_business_value AS embedded_value FROM insurance_data"
                }
            ],
            "测试记录": [
                {
                    "测试日期": "2025-03-15",
                    "测试人员": "袁作滨",
                    "测试结果": "所有指标计算逻辑符合预期",
                    "发现问题": "无"
                }
            ],
            "BUG清单": []
        }
        return test_doc


# 主执行程序
if __name__ == "__main__":
    # 初始化分析器
    analyzer = InsuranceProfitabilityAnalyzer()

    print("正在计算业务指标...")
    results = analyzer.calculate_metrics()

    print("\n关键指标预览:")
    print(results[['report_date', 'roe', 'roa', 'embedded_value', 'underwriting_margin']].tail())

    # 导出结果
    output_file = analyzer.export_to_excel()

    # 生成测试文档内容
    test_doc = analyzer.generate_test_document()
    print("\n测试文档摘要已生成")

    print("\n分析完成!")